package com.kuhh.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.kuhh.dao.ITeachersDao;
import com.kuhh.pojo.Teachers;
import com.kuhh.utils.JdbcUtils;

public class TeachersDaoImpl implements ITeachersDao {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	
	@Override
	public List<Teachers> getTeacherList() {
		List<Teachers> list = new ArrayList<>();
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_teachers order by create_time desc";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Teachers teacher = new Teachers();
				teacher.setTeacherNo(rs.getString(1));
				teacher.setTeacherName(rs.getString(2));
				teacher.setGender(rs.getByte(3));
				teacher.setCreateTime(rs.getDate(4));
				teacher.setUpdateTime(rs.getDate(5));
				list.add(teacher);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public Teachers getTeacherByTeacherNo(String teacherNo) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_teachers where teacher_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, teacherNo);
			rs = ps.executeQuery();
			while(rs.next()) {
				Teachers teacher = new Teachers();
				teacher.setTeacherNo(rs.getString(1));
				teacher.setTeacherName(rs.getString(2));
				teacher.setGender(rs.getByte(3));
				teacher.setCreateTime(rs.getDate(4));
				teacher.setUpdateTime(rs.getDate(5));
				return teacher;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return null;
	}

	@Override
	public boolean addTeacher(Teachers teacher) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "insert into tb_teachers values(?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, teacher.getTeacherNo());
			ps.setString(2, teacher.getTeacherName());
			ps.setByte(3, teacher.getGender());
			ps.setDate(4, new Date(teacher.getCreateTime().getTime()));
			ps.setDate(5, new Date(teacher.getUpdateTime().getTime()));
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean updateTeacher(Teachers teacher) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "update tb_teacher set teacher_name = ?, gender = ?, update_time = ? where teacher_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, teacher.getTeacherName());
			ps.setByte(2, teacher.getGender());
			ps.setDate(3, new Date(teacher.getUpdateTime().getTime()));
			ps.setString(4, teacher.getTeacherNo());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean deleteTeacher(String teacherNo) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "delete from tb_teachers where teacher_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, teacherNo);
			ps.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public List<Teachers> getTeacherUnRegister() {
		// TODO Auto-generated method stub
		List<Teachers> list = new ArrayList<>();
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_teachers where teacher_no not in (select student_teacher_no from tb_user) order by create_time desc";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Teachers teacher = new Teachers();
				teacher.setTeacherNo(rs.getString(1));
				teacher.setTeacherName(rs.getString(2));
				teacher.setGender(rs.getByte(3));
				teacher.setCreateTime(rs.getDate(4));
				teacher.setUpdateTime(rs.getDate(5));
				list.add(teacher);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		} 
		return list;
	}

}
